package org.lvhengchang.schoolmanager.dao;

import java.sql.SQLException;
import java.util.List;

import javax.sql.DataSource;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.lvhengchang.schoolmanager.bean.Student;
import org.lvhengchang.schoolmanager.util.DataSourceTools;
/**
 * 学生数据持久处理类
 * 
 * @author LvHengchang
 *
 */
public class StudentDao {
	

	    private DataSource dataSource = DataSourceTools.getDataSource();

	    private final static String SELECT_ALL =
	            "select student_id, class_id, student_name, student_birthday, sex, remarks from t_students";
	    private final static String SELECT_BY_ID =
	            "select student_id, class_id, student_name, student_birthday, sex, remarks from t_students where student_id = ?";
	    private final static String INSERT_SQL =
	            "insert into t_students(class_id, student_name, student_birthday, sex, remarks) value(?,?,?,?,?)";
	    private final static String UPDATE_SQL =
	            "update t_students set class_id = ?,student_name = ?,student_birthday = ?,sex = ?,remarks = ? where student_id = ?";
	    private final static String DELETE_SQL =
	            "delete from t_students where student_id = ?";
	    private final static String LOGIN_SQL =
	            "select id, username, password, is_delete as 'isDelete' from t_admin where username = ? and password = ? and is_delete=0";

	    
	    public List<Student> selectAll() throws SQLException {
	        QueryRunner queryRunner = new QueryRunner(dataSource);
	        List<Student> students = queryRunner.query(SELECT_ALL,
	                new BeanListHandler<Student>(Student.class));
	        return students;
	    }

	    public Student selectById(String student_id) throws SQLException {
	        QueryRunner queryRunner = new QueryRunner(dataSource);
	        Student student = queryRunner.query(SELECT_BY_ID,
	                new BeanHandler<Student>(Student.class), student_id);
	        return student;

	    }

	    public int insertStudent(Student student) throws SQLException {
	        QueryRunner queryRunner = new QueryRunner(dataSource);
	        int result = queryRunner.update(INSERT_SQL, student.getClass_id(), student.getStudent_name(), 
	        		student.getStudent_birthday(), student.getSex(), student.getRemarks());
	        return result;
	    }

	    public int updateStudent(Student student) throws SQLException {
	        QueryRunner queryRunner = new QueryRunner(dataSource);
	        int result = queryRunner.update(UPDATE_SQL, student.getClass_id(), student.getStudent_name(), 
	        		student.getStudent_birthday(), student.getSex(), student.getRemarks(), student.getStudent_id());
	        return result;
	    }

	    public int deleteStudent(String student_id) throws SQLException {
	        QueryRunner queryRunner = new QueryRunner(dataSource);
	        int result = queryRunner.update(DELETE_SQL, student_id);
	        return result;
	    }
	    
	    public Student loginStudent(String username, String password) throws SQLException {
	        QueryRunner queryRunner = new QueryRunner(dataSource);
	        Student result = queryRunner.query(LOGIN_SQL, new BeanHandler<Student>(Student.class), username, password);
	        return result;
	    }
	    
	    
}

